package org.proxycine.gestsalles.model.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.proxycine.gestsalles.model.entities.Film;
import org.proxycine.gestsalles.model.entities.GenreFilm;

public final class FilmDao extends Adapter {
	
	private static FilmDao instance;
	
	private FilmDao() {
		instance = this;
	}
	
	public static FilmDao getInstance() {
		if(instance == null)
			instance = new FilmDao();
		
		return instance;
	}
	
	/**
	 * 
	 * @param film
	 * @return id of inserted film
	 */
	public int insertFilm(Film film) {
		ResultSet generatedKeys = null;
		int idFilm = -1;

		try {
			PreparedStatement stmt = getConnection()
					.prepareStatement(
							"INSERT INTO Film (id, titre, synopsis, duree, genre, date_sortie, nationalite, realisateur, acteurs, rating)"
									+ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);

			stmt.setString(1, null);
			stmt.setString(2, film.getTitre());
			stmt.setString(3, film.getSynopsis());
			stmt.setInt(4, film.getDuree());
			stmt.setString(5, film.getGenre().name());
			stmt.setDate(6, new java.sql.Date(film.getDateSortie().getTime()));
			stmt.setString(7, film.getNationalite());
			stmt.setString(8, film.getRealisateur());
			stmt.setString(9, film.getActeurs());
			stmt.setDouble(10, film.getRating());

			stmt.executeUpdate();

			generatedKeys = stmt.getGeneratedKeys();
			if (generatedKeys.next()) {
				idFilm = generatedKeys.getInt(1);
			} else {
				throw new SQLException(
						"Creating film failed, no generated key obtained.");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (generatedKeys != null)
				try {
					generatedKeys.close();
				} catch (SQLException logOrIgnore) {}
		}

		return idFilm;
	}
	
	public List<Film> getFilmsDisponibles (){
		List<Film> films = new ArrayList<Film>();
		
		String request = "SELECT DISTINCT f.*  FROM Copie c, Film f WHERE c.etat = 'Achete' AND f.id = c.film" ;
		
		try {
			ResultSet rs = getStatement().executeQuery(request);
			
			while (rs.next()) {
				films.add(new Film(rs.getInt("id"),
						rs.getString("titre"),
						rs.getString("synopsis"),
						rs.getInt("duree"), 
						GenreFilm.valueOf(rs.getString("genre")), 
						rs.getDate("date_sortie"),
						rs.getString("nationalite"),
						rs.getString("realisateur"),
						rs.getString("acteurs"),
						rs.getDouble("rating")));
			}
			
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return films;
	}
	
	public void removeFilm(int id) {
		
		try {
			PreparedStatement stmt = getConnection()
						.prepareStatement("DELETE from Film where id = ?");
			stmt.setInt(1, id);					
			stmt.executeUpdate();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	public Film getFilm(String titre) {
		Film f = null;
		
		String request = "SELECT * from Film " + 
				"WHERE titre = '" + titre + "'";
			try {
				ResultSet rs = getStatement().executeQuery(request);
				
				if (rs.next()) {
					f = new Film(rs.getInt("id"),
							rs.getString("titre"),
							rs.getString("synopsis"),
							rs.getInt("duree"), 
							GenreFilm.valueOf(rs.getString("genre")), 
							rs.getDate("date_sortie"),
							rs.getString("nationalite"),
							rs.getString("realisateur"),
							rs.getString("acteurs"),
							rs.getDouble("rating"));
				}
				
				rs.close();
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		
		return f;
	}
	
	public Film getFilm(int id) {
		Film f = null;
		
		String request = "SELECT * from Film " + 
				"WHERE id = '" + id + "'";
			try {
				ResultSet rs = getStatement().executeQuery(request);
				
				if (rs.next()) {
					f = new Film(rs.getInt("id"),
							rs.getString("titre"),
							rs.getString("synopsis"),
							rs.getInt("duree"), 
							GenreFilm.valueOf(rs.getString("genre")), 
							rs.getDate("date_sortie"),
							rs.getString("nationalite"),
							rs.getString("realisateur"),
							rs.getString("acteurs"),
							rs.getDouble("rating"));
				}
				
				rs.close();
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		
		return f;
	}
	
	public List<Film> getAllFilms() {
		List<Film> films = new ArrayList<Film>();
		
		String request = "SELECT * from Film";
		try {
			ResultSet rs = getStatement().executeQuery(request);
			
			while (rs.next()) {
				films.add(new Film(rs.getInt("id"),
						rs.getString("titre"),
						rs.getString("synopsis"),
						rs.getInt("duree"), 
						GenreFilm.valueOf(rs.getString("genre")), 
						rs.getDate("date_sortie"),
						rs.getString("nationalite"),
						rs.getString("realisateur"),
						rs.getString("acteurs"),
						rs.getDouble("rating")));
			}
			
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return films;
	}
	
	public void updateFilm(Film film) {
		try {
			PreparedStatement stmt = getConnection()
					.prepareStatement(
							"UPDATE Film SET titre = ?," +
							" synopsis = ?," +
							" duree = ?," +
							" genre = ?," +
							" date_sortie = ?," +
							" nationalite = ?," +
							" realisateur = ?," +
							" acteurs = ?," +
							" rating = ?" +
							" WHERE id = ?");

			stmt.setString(1, film.getTitre());
			stmt.setString(2, film.getSynopsis());
			stmt.setInt(3, film.getDuree());
			stmt.setString(4, film.getGenre().name());
			stmt.setDate(5, new java.sql.Date(film.getDateSortie().getTime()));
			stmt.setString(6, film.getNationalite());
			stmt.setString(7, film.getRealisateur());
			stmt.setString(8, film.getActeurs());
			stmt.setDouble(9, film.getRating());
			stmt.setInt(10, film.getId());

			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
		
	public List<Film> getAffectedFilms(String login)
	{
		List<Film> affectedFilms = new ArrayList<Film>();
		String request =	"SELECT F.* " +
							"FROM Employe E, Cinema Ci, Copie Co, Film F " +
							"WHERE Co.cinema = Ci.id " +
							"AND Ci.gerant = E.login " +
							"AND Co.film = F.id " +
							"AND E.login = '" + login + "'" ;

		try {
			ResultSet rs = getStatement().executeQuery(request);
			
			while (rs.next()) {
				affectedFilms.add(new Film(rs.getInt("id"),
						rs.getString("titre"),
						rs.getString("synopsis"),
						rs.getInt("duree"), 
						GenreFilm.valueOf(rs.getString("genre")), 
						rs.getDate("date_sortie"),
						rs.getString("nationalite"),
						rs.getString("realisateur"),
						rs.getString("acteurs"),
						rs.getDouble("rating")));
			}
			
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return affectedFilms;		
	}
	
}
